package com.cirkana.viewer.data;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.cirkana.data.DataException;
import com.cirkana.data.DataNotFoundException;
import com.cirkana.data.IDataColumnDef;
import com.cirkana.data.IDataCriteria;
import com.cirkana.data.IDataProvider;
import com.cirkana.data.IDataRecord;
import com.cirkana.data.IDataTableDef;

public class LocalDataProvider implements IDataProvider {

	private Context context;
	private SQLiteOpenHelperCirkanaImpl openHelper;
	private SQLiteDatabase database;

	private SimpleDateFormat sqLiteDateFormat = new SimpleDateFormat("EEE MMM dd HH:mm:ss z yyyy");

	public LocalDataProvider(Context context, LocalDataConfig config) {
		if (context != null) {
			this.context = context;
			this.openHelper = new SQLiteOpenHelperCirkanaImpl(this.context, config);
			this.database = this.openHelper.getWritableDatabase();
		}
	}

	@Override
	public void begin() {
		this.database.beginTransaction();
	}
	
	@Override
	public void commit() {
		this.database.setTransactionSuccessful();
		this.database.endTransaction();
	}
	
	@Override
	public void rollback() {
		this.database.endTransaction();
	}
	
	@Override
	public ArrayList<IDataRecord> select(IDataTableDef tableDef) throws DataException {
		return select(tableDef, null);
	}

	@Override
	public ArrayList<IDataRecord> select(IDataTableDef tableDef, ArrayList<IDataCriteria> criterion) throws DataException {
		Cursor cursor = this.database.rawQuery(getSelectSql(tableDef, criterion), null);
		return toRecords(tableDef, cursor);
	}

	@Override
	public Integer insert(IDataTableDef tableDef, IDataRecord dataRecord) throws DataException {
		this.database.execSQL(getInsertSql(tableDef), getInsertValues(tableDef, dataRecord));
		return getLastInsertedId();
	}

	@Override
	public void update(IDataTableDef tableDef, IDataRecord dataRecord) throws DataException {
		this.database.execSQL(getUpdateSql(tableDef), getUpdateValues(tableDef, dataRecord));
	}

	@Override
	public void delete(IDataTableDef tableDef, IDataRecord dataRecord) throws DataException {
		this.database.execSQL(getDeleteSql(tableDef), getDeleteValues(tableDef, dataRecord));
	}

	protected String[] extractColumnNames(IDataTableDef tableDef) {
		String[] names = new String[tableDef.getColumnsDef().size()];
		for (int iii = 0; iii < names.length; iii++)
			names[iii] = tableDef.getColumnsDef().get(iii).getColumnName();
		return names;
	}

	protected int getLastInsertedId() throws DataException {
		Cursor cursor = null;
		try {
			cursor = this.database.rawQuery("SELECT last_insert_rowid();", null);
			cursor.moveToFirst();
			return cursor.getInt(0);
		} catch (Exception e) {
			throw new DataException(e);
		} finally {
			if (cursor != null)
				if (!cursor.isClosed())
					cursor.close();
		}
	}

	protected String getSelectSql(IDataTableDef tableDef, ArrayList<IDataCriteria> criterion) throws DataException {
		StringBuilder select = new StringBuilder("SELECT * FROM ");
		select.append(tableDef.getTableName());
		if (criterion != null)
			if (criterion.size() > 0) {
				select.append(" WHERE ");
				for (IDataCriteria criteria : criterion) {
					select.append(criteria.getSelection());
					select.append(" AND ");
				}
				select.delete(select.length() - 5, select.length());
			}
		select.append(";");
		return select.toString();
	}

	protected String getInsertSql(IDataTableDef tableDef) {
		StringBuilder insertInto = new StringBuilder("INSERT INTO ");
		insertInto.append(tableDef.getTableName());
		insertInto.append(" (");
		StringBuilder values = new StringBuilder("VALUES (");
		for (IDataColumnDef column : tableDef.getColumnsDef())
			if (!column.isAutoKey()) {
				insertInto.append(column.getColumnName());
				insertInto.append(", ");
				values.append("?, ");
			}
		insertInto.delete(insertInto.length() - 2, insertInto.length());
		insertInto.append(") ");
		values.delete(values.length() - 2, values.length());
		values.append(");");
		return insertInto.toString() + values.toString();
	}

	protected String getUpdateSql(IDataTableDef tableDef) {
		StringBuilder update = new StringBuilder("UPDATE ");
		update.append(tableDef.getTableName());
		update.append(" SET ");
		for (IDataColumnDef column : tableDef.getColumnsDef())
			if (!column.isKey()) {
				update.append(column.getColumnName());
				update.append(" = ?, ");
			}
		update.delete(update.length() - 2, update.length());
		update.append(" WHERE ");
		for (IDataColumnDef column : tableDef.getColumnsDef())
			if (column.isKey()) {
				update.append(column.getColumnName());
				update.append(" = ? AND ");
			}
		update.delete(update.length() - 5, update.length());
		update.append(";");
		return update.toString();
	}

	protected String getDeleteSql(IDataTableDef tableDef) {
		StringBuilder delete = new StringBuilder("DELETE FROM ");
		delete.append(tableDef.getTableName());
		delete.append(" WHERE ");
		for (IDataColumnDef column : tableDef.getColumnsDef())
			if (column.isKey()) {
				delete.append(column.getColumnName());
				delete.append(" = ? AND ");
			}
		delete.delete(delete.length() - 5, delete.length());
		delete.append(";");
		return delete.toString();
	}

//	protected Object[] getSelectValues(IDataTableDef tableDef, ArrayList<IDataCriteria> criterion) {
//		ArrayList<Object> values = null;
//		if (criterion != null) {
//			if (criterion.size() > 0) {
//				values = new ArrayList<Object>();
//				for (IDataCriteria criteria : criterion)
//					values.add(criteria.getValue());
//			}
//			return values.toArray();
//		} else
//			return null;
//	}

	protected Object[] getInsertValues(IDataTableDef tableDef, IDataRecord dataRecord) {
		ArrayList<Object> values = new ArrayList<Object>();
		for (IDataColumnDef column : tableDef.getColumnsDef())
			if (!column.isAutoKey())
				values.add(dataRecord.getField(column.getColumnName()));
		return values.toArray();
	}

	protected Object[] getUpdateValues(IDataTableDef tableDef, IDataRecord dataRecord) {
		ArrayList<Object> values = new ArrayList<Object>();
		for (IDataColumnDef column : tableDef.getColumnsDef())
			if (!column.isKey())
				values.add(dataRecord.getField(column.getColumnName()));
		for (IDataColumnDef column : tableDef.getColumnsDef())
			if (column.isKey())
				values.add(dataRecord.getField(column.getColumnName()));
		return values.toArray();
	}

	protected Object[] getDeleteValues(IDataTableDef tableDef, IDataRecord dataRecord) {
		ArrayList<Object> values = new ArrayList<Object>();
		for (IDataColumnDef column : tableDef.getColumnsDef())
			if (column.isKey())
				values.add(dataRecord.getField(column.getColumnName()));
		return values.toArray();
	}

	protected String getSelectionString(ArrayList<IDataCriteria> criterion) throws DataException {
		if (criterion == null)
			return null;
		StringBuilder s = new StringBuilder();
		for (IDataCriteria criteria : criterion) {
			s.append(criteria.getSelection());
			s.append(" AND ");
		}
		if (s.length() > 0)
			s.delete(s.length() - 5, s.length());
		return s.toString();
	}

	protected ArrayList<IDataRecord> toRecords(IDataTableDef tableDef, Cursor cursor) throws DataException {
		ArrayList<IDataRecord> records = new ArrayList<IDataRecord>();
		if (!cursor.moveToFirst())
			throw new DataNotFoundException();
		do {
			IDataRecord dataRecord;
			try {
				dataRecord = (IDataRecord) tableDef.getDataRecordType().newInstance();
			} catch (Exception e) {
				throw new DataException(e);
			}
			for (IDataColumnDef column : tableDef.getColumnsDef())
				dataRecord.setField(column.getColumnName(), extractValue(cursor, column));
			records.add(dataRecord);
		} while (cursor.moveToNext());
		return records;
	}

	protected Object extractValue(Cursor cursor, IDataColumnDef column) {
		Object value = null;
		if (column.getColumnType().equals(int.class))
			value = cursor.getInt(column.getColumnPosition());

		if (column.getColumnType().equals(String.class))
			value = cursor.getString(column.getColumnPosition());

		if (column.getColumnType().equals(Date.class)) {
			try {
				String date = cursor.getString(column.getColumnPosition());
				if (date != null)
					value = sqLiteDateFormat.parse(date);
			} catch (ParseException e) {
			}
		}
		return value;
	}

	@Override
	public ArrayList<String> getFilesList() {
		String[] files = this.context.fileList();
		ArrayList<String> filesList = new ArrayList<String>();
		for (String file : files)
			filesList.add(file);
		return filesList;
	}

	@Override
	public ArrayList<String> getFilesList(String extension) {
		ArrayList<String> filesList = getFilesList();
		ArrayList<String> filteredFilesList = new ArrayList<String>();
		for (String file : filesList)
			if (file.endsWith(extension))
				filteredFilesList.add(file);
		return filteredFilesList;
	}

	@Override
	public FileInputStream openFile(String fileName) throws DataException {
		try {
			return this.context.openFileInput(fileName);
		} catch (FileNotFoundException e) {
			throw new DataException(e);
		}
	}

	@Override
	public void saveFile(String fileName, FileInputStream fileInputStream) throws DataException {
		FileOutputStream fileOutputStream = null;
		try {
			fileOutputStream = this.context.openFileOutput(fileName, Context.MODE_PRIVATE);
			int b = fileInputStream.read();
			while (b > -1) {
				fileOutputStream.write(b);
				b = fileInputStream.read();
			}
		} catch (IOException e) {
			throw new DataException(e);
		} finally {
			try {
				fileInputStream.close();
			} catch (IOException e) {
				throw new DataException(e);
			}
			try {
				fileOutputStream.close();
			} catch (IOException e) {
				throw new DataException(e);
			}
		}
	}
}
